import pymysql
import xlrd

def concatSql(sql1,names):
    # 将表头拼接到sql1语句里 : 形成 values(%s,%s,%s.....)
    for i in range(len(names)):
        if i == len(names) - 1:
            sql1 = sql1 + "%s)"
            break
        else:
            sql1 = sql1 + "%s, "
    return sql1

# 读取excel数据传入数据库
def Excel_to_DB(filename,sheet):
    wb = xlrd.open_workbook(filename=filename)
    # 获取选项卡
    sql1 = "insert into user_info values("
    # 默认excel表里的第一行就是数据库表的表头
    index = 0
    if sheet.isdigit():  # 判断是否是通过角标来获取选项卡的
        sheet = int(sheet)  # 将“0” 转换成 0
        st = wb.sheet_by_index(sheet)  # 通过角标获取该选项卡
        # 将第一行当成表头，放到列表["用户名","密码","年龄","地址"]
        names = st.row_values(0)
        sql1 = concatSql(sql1, names)
    else:
        st = wb.sheet_by_name(sheet)
        names = st.row_values(0)
        sql1 = concatSql(sql1, names)

    # 从第二行开始，将所有数据放入param，并批量执行
    param = []
    for i in range(1,st.nrows):
        param.append(st.row_values(i))

    # 批量执行sql语句
    cursor.executemany(sql1,param)
    con.commit()
    cursor.close()
    con.close()

host = "localhost"
user = "root"
password = ""
database = "staff"

con = pymysql.connect(host=host,user=user,password=password,database=database)
cursor = con.cursor()
filename = "D:/pythonProject/excel/baidu-员工的人员信息.xls"
sheet = "0"
Excel_to_DB(filename,sheet)